package com;



import java.lang.reflect.Field;
import java.lang.reflect.Method;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;

import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.Map.Entry;

import javax.jms.Session;
import javax.servlet.http.HttpServletRequest;

import com.syslog.SysLogServices;
import com.util.Component;
import com.util.Util;


import net.sf.json.JSONObject;
import net.sf.json.JSONSerializer;


import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import cn.sfkj.util.DBAccess;


//基础公用类
public class PublicSystem {
	private static final PublicSystem instance = new PublicSystem();
	Const Const=new Const();
	
	private DBAccess dbAccess = new DBAccess(Const.JNDI);

	
	private PublicSystem ()
	{	
		
	}
	public static PublicSystem getInstance ()
	{
		return instance;
	}

	public String getIdMum(int seq,int num) throws Exception
	{
		 String str = String.format("%0"+num+"d", seq);   
		 return str;
		
	}
	/**
	 * 主键生成方式
	 * @param seq seq名称
	 * @return    主键ID
	 * @throws Exception
	 */
	public int getId(String seq) throws Exception
	{
		return Integer.parseInt(getDbAccess().selectOne("select "+seq+".nextval from dual"));
	}
	 /**
     * @return the dbAccess
     */
    public DBAccess getDbAccess() {
        return dbAccess;
    }

	/**
	 * 取得一个数据库连接
	 * @return 
	 */
	public Connection getConnection()
	{
		return getDbAccess().getConnection();
	}
	//* 事务管理模板
	
	/**
	 * 开始事务
	 */
	public void BeginTransaction()
	{
		getDbAccess().BeginTransaction();
	}
	
   
    /**
     * 事务处理方法
     */
    public void updateDataTransaction(String sql) throws Exception
    {
    
    	getDbAccess().updateDataTransaction(sql);
    }

    /**
     * 结束事务
     */
    public void endTransaction() throws Exception
    {
    	 getDbAccess().endTransaction();
    	
    }
    /**
     * 执行SQL语句
     * @param sql sql字符串(拼接方式)
     * @throws Exception
     */
    public void executeSql(String sql) throws Exception
    {
    	getDbAccess().insert(sql);
    }
    
    
    /**
     * 执行SQL语句(带事务)
     * @param sql sql字符串(拼接方式)
     * @throws Exception
     */
    public void executeSqlTransaction(String sql) throws Exception
    {
    	getDbAccess().updateDataTransaction(sql);
    }
    	  
    /**
	 * 查询单数据(Vector)
	 * @param sql SQL语句
	 * @return
	 * @throws Exception
	 */
	public Vector SelResultVec(String sql) throws Exception {
		
		try
		{
		Vector result = dbAccess.select(sql);
		return result;
		}
		catch(Exception ex)
		{
			return null;
		}
		

	}
	
	/**
	 * 查询单数据(String)
	 * @param sql SQL语句
	 * @return 单条记录某字段值
	 * @throws Exception
	 */
	public String SelResultStr(String sql) throws Exception {
		
		try
		{
		String result = dbAccess.selectOne(sql);
		return result;
		}
		catch(Exception ex)
		{
			return null;
		}
		

	}
	
	/**
	 * 单条BLOB数据生成相应文件
	 * @param sql sql语句
	 * @param fileName  文件名
	 * @param file      文件路径
	 * @return
	 * @throws Exception
	 */
	public boolean SelResultBlob(String sql, String file) throws Exception {
		 boolean flag=true;
		try
		{  
			flag =dbAccess.selectBlob(sql,file);	
		}
		catch(Exception ex)
		{
			return false;
		}
		 return flag;
	}
	/**
	 * 单条BLOB数据生成相应文件批量
	 * @param sql sql语句
	 * @param fileName  文件名
	 * @param file      文件路径
	 * @return
	 * @throws Exception
	 */
	public boolean selectBlobTransaction(String sql, String file) throws Exception {
		 boolean flag=true;
		try
		{  
			flag =dbAccess.selectBlobTransaction(sql,file);	
		}
		catch(Exception ex)
		{
			return false;
		}
		 return flag;

	}
	/**
	 * 单条BLOB数据生成相应文件
	 * @param sql sql语句
	 * @param fileName  文件名
	 * @param file      文件路径
	 * @return
	 * @throws Exception
	 */
	public boolean excuteBlobTransaction(String sql,String file) throws Exception {
		 boolean flag=true;
		 FileInputStream fi=null;
		try
		{  
		    fi=new FileInputStream(file); 
			flag = dbAccess.excuteBlobTransaction(sql,fi);
		}
		catch(Exception ex)
		{
			return false;
		}
		finally
		{
			fi.close();
		}
		 return flag;

	}
	
	
	/**
	 * 单条BLOB数据生成相应文件
	 * @param sql sql语句
	 * @param fileName  文件名
	 * @param file      文件路径
	 * @return
	 * @throws Exception
	 */
	public boolean excuteBlob(String sql,String file) throws Exception {
		 boolean flag=true;
		 FileInputStream fi=null;
		try
		{  
		    fi=new FileInputStream(file); 
			flag = dbAccess.excuteBlob(sql,fi);
		}
		catch(Exception ex)
		{
			return false;
		}
		finally
		{
			fi.close();
		}
		 return flag;

	}
	
	
	
	/**
	 * 查询数据(List)
	 * @param sql SQL语句
	 * @return 返回List类型数据
	 * @throws Exception
	 */
	public List SelResultList(String sql) throws Exception {
		
		try
		{
		List result = dbAccess.ExecQuery(sql);
		return result;
		}
		catch(Exception ex)
		{
			return null;
		}
		

	}
	/**
	 * 查询单数据
	 * @param sql SQL语句
	 * @return 返回数组
	 * @throws Exception
	 */
	public String [] SelResultNum(String sql) throws Exception {
		
		Vector result = getDbAccess().select(sql);
		String reslut="";
		int sqlCont=SqlCount(sql);
		String xml []= new String [sqlCont];

		for (int i = 0; i < sqlCont; i++) {
			try
			{
			reslut=((Vector) result.elementAt(0)).elementAt(i).toString()
			.trim();
			}
			catch (Exception wx)
			{
				reslut=" ";
			}

			xml[i] = reslut ;

		}
		return xml;
	}
	
	/**
	 * SQL数据录入操作
	 * @param sql  语句
	 * @param data map数据 （注意：map中的键必须与数据库的字段保持一致）
	 * @throws Exception
	 */
	public void InesrtData(String sql,HashMap data)
    throws Exception
    {
		Connection con = null;
		PreparedStatement stmt = null;
		try
		{
			String col[]=sql.substring(sql.indexOf('(')+1,sql.indexOf(')')).split(",");
			con = getConnection();
			stmt = con.prepareStatement(sql);
			for(int i=0;i<col.length;i++){  
				//取得map和字段的对应值
				String mapname=col[i].trim();
				//判断字符串字段是否是日期
				boolean isflag=Component.isValidDate((String)data.get(mapname));
				if(isflag==true)
				{
					try
					{
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
						Date date1 = new Date();
						date1 = sdf.parse((String)data.get(mapname));
						stmt.setObject(i+1, new java.sql.Date(date1.getTime()));
					}catch(Exception c)
					{
						stmt.setObject(i+1, data.get(mapname));
					}
				}
				else
				{
					stmt.setObject(i+1, data.get(mapname));
				}
			}
			stmt.executeUpdate();	
			con.commit();
		}
		catch(Exception ex)
		{
			throw ex;
		}
		finally
		{
			try
			{
				stmt.close();
				con.close();
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}
		return;
    }
	
	/**
	 * SQL更新数据操作
	 * @param sql  sql语句
	 * @param data map对象
	 * @param wherecol 条件语句
	 * @param obj	   条件对象
	 * @throws Exception
	 */
	public void UpdateData(String sql,HashMap data,String wherecol,Object[] obj)
    throws Exception
    {
		Connection con = null;
		PreparedStatement stmt = null;
		try
		{
			String colname=sql.substring(sql.indexOf("set")+3, sql.length());
			String col[]=colname.split(",");
			
		    sql=sql+ "  where "+ wherecol;
			con = getConnection();
			stmt = con.prepareStatement(sql);
			int i=0;
			for(i=0;i<col.length;i++){  
				//取得map和字段的对应值
				String mapname=col[i].substring(0, col[i].length()-2).trim();
				//判断字符串字段是否是日期
				boolean isflag=Component.isValidDate((String)data.get(mapname));
				if(isflag==true)
				{
					try
					{
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
						Date date1 = new Date();
						date1 = sdf.parse((String)data.get(mapname));
						stmt.setObject(i+1, new java.sql.Date(date1.getTime()));
					}catch(Exception c)
					{
						stmt.setObject(i+1, data.get(mapname));
					}
				}
				else
				{
					stmt.setObject(i+1, data.get(mapname));
				}
			}
			for(int j=0;j<obj.length;j++){ 
				stmt.setObject(i+1, obj[j]);
			}  
			stmt.executeUpdate();
			con.commit();
		}
		catch(Exception ex)
		{
			throw ex;
		}
		finally
		{
			try
			{
				stmt.close();
				con.close();
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}
		return;
    }
	
	
	
	/**
	 * 数据库查询操作持久层DAO
	 * @param sql sql语句
	 * @param obj 参数对象
	 * @param flag  默认数据true-列表默认有数据，false-列表默认无数据
	 * @return
	 */
	public List query(String sql,Object[] obj,boolean flag){  
		List list;
        list = new ArrayList();
        if(flag==false)
        {
        	if("1".equals(Util.getObject(obj)))
        	{
        		Connection conn=null;  
        		PreparedStatement pstmt=null;  
        		try{  
        			conn=getConnection();  
        			pstmt=conn.prepareStatement(sql);
			
        			for(int i=0;i<obj.length;i++){
        				pstmt.setObject(i+1, obj[i]);
        			}  
        			ResultSet rs=pstmt.executeQuery();
			
        			ResultSetMetaData rsmd = rs.getMetaData();
        			int numCols = rsmd.getColumnCount();
        			String tempValue = "";
        			HashMap hm;
        			while (rs.next())
        			{
        				hm = new HashMap();
        				for(int i = 1; i <= numCols; i++)
        				{
        					tempValue = rs.getString(i);
        					if(tempValue == null)tempValue = "";
        					hm.put(rsmd.getColumnLabel(i).toLowerCase(), tempValue.trim());
        				}
        				list.add(hm);

        			}
        		}catch(SQLException ex){  
        			ex.printStackTrace();  
        		}finally{  
        			try{  
        				pstmt.close();  
        				conn.close();  
        			}catch(SQLException ex){  
        				ex.printStackTrace();  
        			}  
        		}  
        	}
        }else
        {
        	Connection conn=null;  
    		PreparedStatement pstmt=null;  
    		try{  
    			conn=getConnection();  
    			pstmt=conn.prepareStatement(sql);
    			if (null==obj||0==obj.length)
    	        {	
    	        }
    			else
    			{
    				for(int i=0;i<obj.length;i++){
    				pstmt.setObject(i+1, obj[i]);
    				}  
    			}
    			ResultSet rs=pstmt.executeQuery();
		
    			ResultSetMetaData rsmd = rs.getMetaData();
    			int numCols = rsmd.getColumnCount();
    			String tempValue = "";
    			HashMap hm;
    			while (rs.next())
    			{
    				hm = new HashMap();
    				for(int i = 1; i <= numCols; i++)
    				{
    					tempValue = rs.getString(i);
    					if(tempValue == null)tempValue = "";
    					hm.put(rsmd.getColumnLabel(i).toLowerCase(), tempValue.trim());
    				}
    				list.add(hm);

    			}
    		}catch(SQLException ex){  
    			ex.printStackTrace();  
    		}finally{  
    			try{  
    				pstmt.close();  
    				conn.close();  
    			}catch(SQLException ex){  
    				ex.printStackTrace();  
    			}  
    		}  
    	}

		return list;  
		}  
	
	/**
	 * SQL数据删除操作
	 * @param sql sql语句
	 * @param obj 参数对象
	 */
	public void RemoveData(String sql,Object[] obj){  
		
		Connection conn=null;  
		PreparedStatement pstmt=null;  
		try{  
			conn=getConnection();  
			pstmt=conn.prepareStatement(sql); 
			if (obj != null )
	        {
				for(int i=0;i<obj.length;i++){ 
					pstmt.setObject(i+1, obj[i]);
				}  
	        }
			pstmt.executeUpdate();
			conn.commit();
			}catch(SQLException ex){  
				ex.printStackTrace();  
			}finally{  
				try{  
					pstmt.close();  
					conn.close();  
				}catch(SQLException ex){  
				ex.printStackTrace();  
				}  
			}  
	
		}  
	
	
	/**
	 * GET Grid分页排序数据
	 * @param str
	 * @param request
	 * @return
	 */
	public HashMap getGridSort(HttpServletRequest request) throws Exception
	{
		HashMap parms= new HashMap();
		//分页
		parms.put("pageIndex", Component.getQuest("pageIndex", request));
		parms.put("pageSize", Component.getQuest("pageSize", request));
         
        //字段排序
		parms.put("sortField", Component.getQuest("sortField", request));
		parms.put("sortOrder", Component.getQuest("sortOrder", request));
		return parms;
	}
	
	
	  /**
	   * grid表单生成公用方法
	   * @param sql sql语句
	   * @param obj 参数对象
	   * @param parms 排序分页对象
	   * @return map
	   * @throws Exception
	   */
	  public HashMap getGrid(String sql,Object [] obj,HashMap parms,boolean flag) throws Exception
	    {
		  	String sortOrder=(String)parms.get("sortOrder");
		  	String sortField=(String)parms.get("sortField");
	        if (Util.isNullOrEmpty(sortField) == false)
	        {
	            if ("desc".equals(sortOrder) == false) sortOrder = "asc";
	            sql += " order by " + sortField + " " + sortOrder;
	        }
	        else
	        {
	            sql += " ";
	        }

	        List dataAll = query(sql, obj,flag);
	        
	        ArrayList data = new ArrayList();
	        
	        int start = Integer.parseInt(parms.get("pageIndex").toString()) * Integer.parseInt(parms.get("pageSize").toString()), end = start +  Integer.parseInt(parms.get("pageSize").toString());

	        for (int i = 0, l = dataAll.size(); i < l; i++)
	        {
	            HashMap record = (HashMap)dataAll.get(i);
	            if (record == null) continue;
	            if (start <= i && i < end)
	            {
	                data.add(record);
	            } 
	        }
	        HashMap result = new HashMap();
	        result.put("data", data);
	        result.put("total", dataAll.size());
	        return result;
	    
	    }
	  	
	  

	  /**
	   * 录入持久层DAO
	   * @param entity  vo对象
	   * @throws SQLException
	   * @throws IllegalArgumentException
	   * @throws InvocationTargetException
	   * @throws IllegalAccessException
	   */
	  public void Jdbc_saveDao(Object entity) throws SQLException,IllegalArgumentException,InvocationTargetException ,IllegalAccessException 
	  {
		  Class<? extends Object> clazz = entity.getClass();
		  Method[] methods = clazz.getDeclaredMethods();
		  Field[] fields = clazz.getDeclaredFields();
		  String tableName = clazz.getSimpleName().toLowerCase();

		  String fieldsName = "";
		  String placeholder = "";
		  for (int i = 0; i < fields.length; i++) {
			    fieldsName = fieldsName + fields[i].getName() + ",";
	  			placeholder = placeholder + "?" + ",";
		  }

		  fieldsName = fieldsName.substring(0, fieldsName.length() - 1);
		  placeholder = placeholder.substring(0, placeholder.length() - 1);

		  String sql = "insert into " + tableName + "(" + fieldsName + ")"
		  + " values " + "(" + placeholder + ")";

		  System.out.println(sql);
	  
		 Connection con=null; PreparedStatement pst =null;Object propertyObj=null;
		 try
		 {
			 con=getConnection();
			 pst = con.prepareStatement(sql);
			 int index = 1;

			 for (int j = 0; j < fields.length; j++) {

				 String str = "get" + fields[j].getName();
				 String type=fields[j].getType().toString();
				 for (int k = 0; k < methods.length; k++) {
					 if (str.equalsIgnoreCase(methods[k].getName())) {
						
						 propertyObj = methods[k].invoke(entity);
						 //System.out.println(str+"======="+propertyObj+ "--"+type);
						 if(null==propertyObj||"null".equals(propertyObj))
							 {propertyObj=" ";}
						
						 if("class java.util.Date".equals(type)){
							
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.DATE);
							 else pst.setObject(index++, new java.sql.Date(((Date) propertyObj).getTime()));
						 }
						 else if("int".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.INTEGER);
							 else pst.setObject(index++, propertyObj);
						 }
						 else if("class [B".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.BLOB);
							 else pst.setObject(index++, propertyObj);
						 }
						 else
						 {
							 pst.setObject(index++, propertyObj);
						 }
					 }

				 }
			 }
			 pst.executeUpdate();
			 con.commit();
			 System.out.println("---------------------------------");
			 
		}catch(SQLException ex){  
				ex.printStackTrace();  
		}finally{  
			try{  
				pst.close();
				con.close();  
			}catch(SQLException ex){  
				ex.printStackTrace();  
			}  
		}  
	}
	  
	  
	  /**
	   * 录入持久层DAO事务管理
	   * @param entity  vo对象
	   * @throws SQLException
	   * @throws IllegalArgumentException
	   * @throws InvocationTargetException
	   * @throws IllegalAccessException
	   */
	  public void Jdbc_saveDaoTransaction(Object entity) throws SQLException,IllegalArgumentException,InvocationTargetException ,IllegalAccessException 
	  {
		  Class<? extends Object> clazz = entity.getClass();
		  Method[] methods = clazz.getDeclaredMethods();
		  Field[] fields = clazz.getDeclaredFields();
		  String tableName = clazz.getSimpleName().toLowerCase();

		  String fieldsName = "";
		  String placeholder = "";
		  for (int i = 0; i < fields.length; i++) {
			    fieldsName = fieldsName + fields[i].getName() + ",";
	  			placeholder = placeholder + "?" + ",";
		  }

		  fieldsName = fieldsName.substring(0, fieldsName.length() - 1);
		  placeholder = placeholder.substring(0, placeholder.length() - 1);

		  String sql = "insert into " + tableName + "(" + fieldsName + ")"
		  + " values " + "(" + placeholder + ")";

		  System.out.println(sql);
	  
		 Connection con=null; PreparedStatement pst =null;Object propertyObj=null;
		 try
		 {
				con=dbAccess.getTransaction();
				if(null!=con){
			 	pst = con.prepareStatement(sql);
			 	int index = 1;
			 	for (int j = 0; j < fields.length; j++) {

				 String str = "get" + fields[j].getName();
				 String type=fields[j].getType().toString();
				 for (int k = 0; k < methods.length; k++) {
					 if (str.equalsIgnoreCase(methods[k].getName())) {
						
						 propertyObj = methods[k].invoke(entity);
						 //System.out.println(str+"======="+propertyObj+ "--"+type);
						 if(null==propertyObj||"null".equals(propertyObj))
							 {propertyObj=" ";}
						
						 if("class java.util.Date".equals(type)){
							
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.DATE);
							 else pst.setObject(index++, new java.sql.Date(((Date) propertyObj).getTime()));
						 }
						 else if("int".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.INTEGER);
							 else pst.setObject(index++, propertyObj);
						 }
						 else if("class [B".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.BLOB);
							 else pst.setObject(index++, propertyObj);
						 }
						 else
						 {
							 pst.setObject(index++, propertyObj);
						 }
					 }

				 }
			 }
			 pst.executeUpdate();	  
			 
			
			 System.out.println("---------------------------------");
			}
	
		}catch(SQLException ex){  
				ex.printStackTrace(); 
				pst.close();
    			con.rollback();
    			try{
    			endTransaction();}catch(Exception f){}
    			System.out.println("发生严重错误!事务处理失败!");	
		}finally{  
			try{  
				if (pst != null) 
	           	{  
					pst.close();  
	           	}  
				
			}catch(SQLException ex){  
				ex.printStackTrace();  
			}  
		}  
	}
	  
	  /**
	   * 修改持久层DAO
	   * @param entity  修改vo对象名
	   * @throws SQLException
	   * @throws IllegalArgumentException
	   * @throws InvocationTargetException
	   * @throws IllegalAccessException
	   */
	  public void Jdbc_modifyDaoTransaction(Object entity,Object obj[]) throws SQLException,IllegalArgumentException,InvocationTargetException ,IllegalAccessException 
	  {
		  Class<? extends Object> clazz = entity.getClass();
		  Method[] methods = clazz.getDeclaredMethods();
		  Field[] fields = clazz.getDeclaredFields();
		  String tableName = clazz.getSimpleName().toLowerCase();

		  String fieldsName = "";String whereName="";
		  Object[] val=new Object[obj.length];
		// 循环遍历以获取的公开的属性

		for (int i = 0; i < fields.length; i++) {

			// 嵌套循环,比较公开属性名经过拼接后和公开的方法名进行匹配
			for (int j = 0; j < methods.length; j++) {

				// 使用属性名+get进行拼接
				String getFieldName = "get" + fields[i].getName();
				if (getFieldName.equalsIgnoreCase(methods[j].getName())) {

					// 拼接更行sql语句中的set字段,并用占位符
					fieldsName = fieldsName + fields[i].getName() + "=?,";

				}
	
			}
		}
		
				// 获取id字段的值
				for(int h=0;h<obj.length;h++)
				{
					String tc="";String link="";
					String col=obj[h].toString().split(":")[0].toUpperCase();
					tc="get"+col;
					for (int j = 0; j < methods.length; j++) {
						if (tc.equalsIgnoreCase(methods[j].getName())) {
							val[h] = methods[j].invoke(entity);
						}	
					}
					if("A".equals(obj[h].toString().split(":")[1])||"a".equals(obj[h].toString().split(":")[1]))link="AND ";
					else if("O".equals(obj[h].toString().split(":")[1])||"o".equals(obj[h].toString().split(":")[1]))link="OR ";
					else link=" ";
					if(h==obj.length-1)link=" ";
					whereName=whereName+col+"=? "+link;
				}
		
		
		fieldsName = fieldsName.substring(0, fieldsName.length() - 1);
		String sql = "update " + tableName + " set " + fieldsName

		+ " where "+whereName;
		
		System.out.println(sql);
	  
		 Connection con=null; PreparedStatement pst =null;Object propertyObj=null;
		 try
		 {
			 con=dbAccess.getTransaction();
			 if(null!=con){
			 pst = con.prepareStatement(sql);
			 int index = 1;

			 for (int j = 0; j < fields.length; j++) {

				 String str = "get" + fields[j].getName();
				 String type=fields[j].getType().toString();
				 for (int k = 0; k < methods.length; k++) {
					 if (str.equalsIgnoreCase(methods[k].getName())) {
						
						 propertyObj = methods[k].invoke(entity);
						 //System.out.println(str+"======="+propertyObj+ "--"+type);
						 if(null==propertyObj||"null".equals(propertyObj))
							 {propertyObj=" ";}
						
						 if("class java.util.Date".equals(type)){
							
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.DATE);
							 else pst.setObject(index++, new java.sql.Date(((Date) propertyObj).getTime()));
						 }
						 else if("int".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.INTEGER);
							 else pst.setObject(index++, propertyObj);
						 }
						 else if("class [B".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.BLOB);
							 else pst.setObject(index++, propertyObj);
						 }
						 else
						 {
							 pst.setObject(index++, propertyObj);
						 }
					 }

				 }
			 }
			 for(int h=0;h<obj.length;h++)
			 {
				 pst.setObject(index++, val[h]);
			 }
			 pst.executeUpdate();	  
			 
			 System.out.println("---------------------------------");
			 }
		}catch(SQLException ex){  
			ex.printStackTrace(); 
			pst.close();
			con.rollback();
			try{
			endTransaction();}catch(Exception f){}
			System.out.println("发生严重错误!事务处理失败!");	  
		}finally{  
			try{  
				pst.close();
				
			}catch(SQLException ex){  
				ex.printStackTrace();  
			}  
		}  
	}
	  
	  
	  
	  
	  /**
	   * 修改持久层DAO
	   * @param entity  修改vo对象名
	   * @throws SQLException
	   * @throws IllegalArgumentException
	   * @throws InvocationTargetException
	   * @throws IllegalAccessException
	   */
	  public void Jdbc_modifyDao(Object entity,Object obj[]) throws SQLException,IllegalArgumentException,InvocationTargetException ,IllegalAccessException 
	  {
		  Class<? extends Object> clazz = entity.getClass();
		  Method[] methods = clazz.getDeclaredMethods();
		  Field[] fields = clazz.getDeclaredFields();
		  String tableName = clazz.getSimpleName().toLowerCase();

		  String fieldsName = "";String whereName="";
		  Object[] val=new Object[obj.length];
		// 循环遍历以获取的公开的属性

		for (int i = 0; i < fields.length; i++) {

			// 嵌套循环,比较公开属性名经过拼接后和公开的方法名进行匹配
			for (int j = 0; j < methods.length; j++) {

				// 使用属性名+get进行拼接
				String getFieldName = "get" + fields[i].getName();
				if (getFieldName.equalsIgnoreCase(methods[j].getName())) {

					// 拼接更行sql语句中的set字段,并用占位符
					fieldsName = fieldsName + fields[i].getName() + "=?,";

				}
	
			}
		}
		
				// 获取id字段的值
				for(int h=0;h<obj.length;h++)
				{
					String tc="";String link="";
					String col=obj[h].toString().split(":")[0].toUpperCase();
					tc="get"+col;
					for (int j = 0; j < methods.length; j++) {
						if (tc.equalsIgnoreCase(methods[j].getName())) {
							val[h] = methods[j].invoke(entity);
						}	
					}
					if("A".equals(obj[h].toString().split(":")[1])||"a".equals(obj[h].toString().split(":")[1]))link="AND ";
					else if("O".equals(obj[h].toString().split(":")[1])||"o".equals(obj[h].toString().split(":")[1]))link="OR ";
					else link=" ";
					if(h==obj.length-1)link=" ";
					whereName=whereName+col+"=? "+link;
				}
		
		
		fieldsName = fieldsName.substring(0, fieldsName.length() - 1);
		String sql = "update " + tableName + " set " + fieldsName

		+ " where "+whereName;
		
		System.out.println(sql);
	  
		 Connection con=null; PreparedStatement pst =null;Object propertyObj=null;
		 try
		 {
			 con=getConnection();
			 pst = con.prepareStatement(sql);
			 int index = 1;

			 for (int j = 0; j < fields.length; j++) {

				 String str = "get" + fields[j].getName();
				 String type=fields[j].getType().toString();
				 for (int k = 0; k < methods.length; k++) {
					 if (str.equalsIgnoreCase(methods[k].getName())) {
						
						 propertyObj = methods[k].invoke(entity);
						// System.out.println(str+"======="+propertyObj+ "--"+type);
						 if(null==propertyObj||"null".equals(propertyObj))
							 {propertyObj=" ";}
						
						 if("class java.util.Date".equals(type)){
							
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.DATE);
							 else pst.setObject(index++, new java.sql.Date(((Date) propertyObj).getTime()));
						 }
						 else if("int".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.INTEGER);
							 else pst.setObject(index++, propertyObj);
						 }
						 else if("class [B".equals(type))
						 {	 
							 if(" ".equals(propertyObj))pst.setNull(index++, Types.BLOB);
							 else pst.setObject(index++, propertyObj);
						 }
						 else
						 {
							 pst.setObject(index++, propertyObj);
						 }
					 }

				 }
			 }
			 for(int h=0;h<obj.length;h++)
			 {
				 pst.setObject(index++, val[h]);
			 }
			 pst.executeUpdate();	  
			 con.commit();
			  System.out.println("---------------------------------");
		}catch(SQLException ex){  
				ex.printStackTrace();  
		}finally{  
			try{  
				pst.close();
				con.close();  
			}catch(SQLException ex){  
				ex.printStackTrace();  
			}  
		}  
	}
	  
	  
	  
	 /**
	  * 删除持久层DAO
	  * @param entity	VO对象名称
	  * @param obj      删除参数对象
	  * @throws SQLException
	  * @throws IllegalArgumentException
	  * @throws InvocationTargetException
	  * @throws IllegalAccessException
	  */
	  public void Jdbc_deleteDao(Object entity,Object obj[]) throws SQLException,IllegalArgumentException,InvocationTargetException ,IllegalAccessException 
	  {
		  Class<? extends Object> clazz = entity.getClass();
		  Method[] methods = clazz.getDeclaredMethods();
		  Field[] fields = clazz.getDeclaredFields();
		  String tableName = clazz.getSimpleName().toLowerCase();

		  String fieldsName = "";String whereName="";
		  Object[] val=new Object[obj.length];
	
		
				// 获取id字段的值
				for(int h=0;h<obj.length;h++)
				{
					String tc="";String link="";
					String col=obj[h].toString().split(":")[0].toUpperCase();
					tc="get"+col;
					for (int j = 0; j < methods.length; j++) {
						if (tc.equalsIgnoreCase(methods[j].getName())) {
							val[h] = methods[j].invoke(entity);
						}	
					}
					if("A".equals(obj[h].toString().split(":")[1])||"a".equals(obj[h].toString().split(":")[1]))link="AND ";
					else if("O".equals(obj[h].toString().split(":")[1])||"o".equals(obj[h].toString().split(":")[1]))link="OR ";
					else link=" ";
					if(h==obj.length-1)link=" ";
					whereName=whereName+col+"=? "+link;
				}
		
				String sql = "delete from " + tableName + " where "+whereName;
		
				System.out.println(sql);
				
				Connection con=null; PreparedStatement pst =null;
				try
				{
					con=getConnection();
					pst = con.prepareStatement(sql);
					int index = 1;
					for(int h=0;h<obj.length;h++)
					{
						pst.setObject(index++, val[h]);
					}
					pst.executeUpdate();	  
					con.commit();
					System.out.println("---------------------------------");
				}catch(SQLException ex){  
				ex.printStackTrace();  
				}finally{  
					try{  
						pst.close();
						con.close();  
					}catch(SQLException ex){  
							ex.printStackTrace();  
						}  
				}  
	  }
	  
	  
	  public void Jdbc_deleteDaoTransaction(Object entity,Object obj[]) throws SQLException,IllegalArgumentException,InvocationTargetException ,IllegalAccessException 
	  {
		  Class<? extends Object> clazz = entity.getClass();
		  Method[] methods = clazz.getDeclaredMethods();
		  Field[] fields = clazz.getDeclaredFields();
		  String tableName = clazz.getSimpleName().toLowerCase();

		  String fieldsName = "";String whereName="";
		  Object[] val=new Object[obj.length];
	
		
				// 获取id字段的值
				for(int h=0;h<obj.length;h++)
				{
					String tc="";String link="";
					String col=obj[h].toString().split(":")[0].toUpperCase();
					tc="get"+col;
					for (int j = 0; j < methods.length; j++) {
						if (tc.equalsIgnoreCase(methods[j].getName())) {
							val[h] = methods[j].invoke(entity);
						}	
					}
					if("A".equals(obj[h].toString().split(":")[1])||"a".equals(obj[h].toString().split(":")[1]))link="AND ";
					else if("O".equals(obj[h].toString().split(":")[1])||"o".equals(obj[h].toString().split(":")[1]))link="OR ";
					else link=" ";
					if(h==obj.length-1)link=" ";
					whereName=whereName+col+"=? "+link;
				}
		
				String sql = "delete from " + tableName + " where "+whereName;
		
				System.out.println(sql);
				
				Connection con=null; PreparedStatement pst =null;
				try
				{
					con=dbAccess.getTransaction();
					if(null!=con){
					pst = con.prepareStatement(sql);
					int index = 1;
					for(int h=0;h<obj.length;h++)
					{
						pst.setObject(index++, val[h]);
					}
					pst.executeUpdate();	  
					
					System.out.println("---------------------------------");
					}
				}catch(SQLException ex){  
					ex.printStackTrace(); 
					pst.close();
					con.rollback();
					try{
					endTransaction();}catch(Exception f){}
					System.out.println("发生严重错误!事务处理失败!");	 
				}finally{  
					try{  
						pst.close();
						
					}catch(SQLException ex){  
							ex.printStackTrace();  
						}  
				}  
	  }
		
		/**
		 * 取sql语句列的数量
		 * @param sql语句
		 * @return 返回列数量
		 * @throws Exception
		 */
		private int SqlCount(String sql) throws Exception 
		{
			 PreparedStatement stmt= null;
			 ResultSet rs = null;
			 Connection con = null;
			 int numCols=0;
			 try
		     {
				 con = dbAccess.getConnection();
			 
				 stmt = con.prepareStatement(sql);
				
				 rs = stmt.executeQuery(sql);
				 ResultSetMetaData rsmd = rs.getMetaData();
				 numCols = rsmd.getColumnCount();
	        
			
		     }
	         catch(SQLException e)
		     {
		         System.out.println(e + "\n\nError sql is:\n" + sql);
		     }
		     finally
		     {
		         try
		         {
		             if(rs != null)
		                 rs.close();
		             if(stmt != null)
		                 stmt.close();
		             if(con != null)
		                 try
		                 {
		                     if(!con.isClosed())
		                         con.close();
		                 }
		                 catch(Exception e)
		                 {
		                     System.out.println("链接池关闭失败!");
		                 }
		         }
		         catch(SQLException ex)
		         {
		             ex.printStackTrace();
		         }
		     }

			return numCols;
		}
		
		
		
		/**
		 * 导出excel表格公用方法（带表头）
		 * @param os	导出路径
		 * @param sqle  sql语句
		 * @param title 表头参数
		 * @throws Exception
		 */
		public void writeExcelTitle(OutputStream os,String sqle,String [] title ) throws Exception {
			
			WritableWorkbook wwb = null;
			try
			{
				wwb = Workbook.createWorkbook(os);
				WritableSheet ws = wwb.createSheet("TestSheet1", 0);
		
				Label t[] = new Label[title.length];
				for(int k=0;k<t.length;k++)
				{
					t[k]= new jxl.write.Label(k,0,title[k]);
					ws.addCell(t[k]);
				}
			
			
				sqle=Component.UnicodeToGB2312(sqle);
				Vector sql = getDbAccess().select(sqle);
				int count=SqlCount(sqle);
				Label label[] = new Label[sql.size()];
			
			
				for (int i = 0; i < sql.size(); i++) 
				{
					for(int j=0;j<count;j++)
					{
				
						try
						{
							label[i] = new jxl.write.Label(j, i+1, ((Vector) sql.elementAt(i)).elementAt(j).toString().trim());
						}
						catch(Exception ex)
						{
							label[i] =  new jxl.write.Label(j, i+1,"");
						}
						ws.addCell(label[i]);
					}
			
				}

			wwb.write();
		}catch(Exception f){f.getStackTrace();}
		finally {wwb.close();}
		}
		
		
		
		
		/**
		 * 导出excel表格公用方法
		 * @param os	导出路径
		 * @param sqle  sql语句
		 * @throws Exception
		 */
		public void writeExcel(OutputStream os,String sqle) throws Exception {
					
			sqle=Component.UnicodeToGB2312(sqle);
			Vector sql = getDbAccess().select(sqle);
			int count=SqlCount(sqle);
			Label label[] = new Label[sql.size()];
			
			WritableWorkbook wwb = null;
			
			try
			{
				wwb=Workbook.createWorkbook(os);
				WritableSheet ws = wwb.createSheet("TestSheet1", 0);
			
				for (int i = 0; i < sql.size(); i++) 
				{
					for(int j=0;j<count;j++)
					{
				
						try
						{
							label[i] = new jxl.write.Label(j, i, ((Vector) sql.elementAt(i)).elementAt(j).toString().trim());
						}
						catch(Exception ex)
						{
							label[i] =  new jxl.write.Label(j, i,"");
						}
						ws.addCell(label[i]);
					}
				}
				wwb.write();
			}catch(Exception f){f.getStackTrace();}
			finally {wwb.close();}
			
		}
		
		/**
		 * 调用数据库函数
		 * @param functionName 数据库函数的全称
		 * @param map 传入的值
		 *  key 对应的值：outNumber 输入的值个数 ，inValueStr 传入的值 以“,” 进行分割 并且为全部的传入值
		 * @return 返回值中以“&”分割，前面为函数返回值，后面为返回的传出值以“,”进行分割
		 * @throws SQLException 
		 * @throws Exception
		 */
		public String callFunction(String functionName ,Map<String,Object> map) throws Exception
		{
			
			String valueStr = "";
			String value = "";
			String outValue = "";
			int outNumber = 0;
			
			int inNumber = 0;
			
			if(null != (String)map.get("outNumber")&&!"null".equals((String)map.get("outNumber")) && !"".equals((String)map.get("outNumber")))
			{
				outNumber = Integer.parseInt((String)map.get("outNumber"));
			}
			if(null != (String)map.get("inValueStr")&&!"null".equals((String)map.get("inValueStr")) && !"".equals((String)map.get("inValueStr")))
			{
				inNumber = ((String)map.get("inValueStr")).split(",").length;
			}
			 
			
			
			for(int i = 0 ;i < inNumber + outNumber ; i ++)
			{
				valueStr = valueStr + "?,";
			}
			
			Connection con = null;
			
			CallableStatement callableStatement = null;
			
			try
			{
				con = dbAccess.getConnection();
			
				if(inNumber > 0)
				{
					valueStr = valueStr.substring(0,valueStr.lastIndexOf(","));
				}
			
				callableStatement = con.prepareCall("{? = call "+functionName+"(" + valueStr + ")}");
			
				callableStatement.registerOutParameter(1, Types.VARCHAR);
			
				for(int n = 0 ; n < inNumber ; n ++)
				{
					String[] invalueStr = ((String)map.get("inValueStr")).split(",");
					String invalue = invalueStr[n];
					callableStatement.setString(n + 2, invalue);
				}
				for(int o = 0 ; o < outNumber; o ++)
				{
					callableStatement.registerOutParameter(o + inNumber + 2, Types.VARCHAR);
				}
			
				callableStatement.execute();
			
				 value = callableStatement.getString(1);
			
				
				for (int  j= 0; j < outNumber; j++) 
				{
					outValue = outValue + "," + callableStatement.getString(inNumber + 2 +j);
				}
			
				if(outNumber > 0)
				{
					outValue = outValue.substring(0,outValue.lastIndexOf(","));
				}
			}catch(Exception f)
			{
				f.fillInStackTrace();
			}
			finally
			{
				if (null!=con) con.close();
				if(null!=callableStatement)callableStatement.close();
			}
			
			return value +"&"+ outValue;
		}
		
		/**
		 * 调用oracle数据库函数和过程
		 * @param functionName
		 * @param type 调用类型 function是调用函数  ；procedure 是过程
		 * @param map 传入的值
		 * @return 返回值中以“&”分割，前面为函数返回值，后面为返回的传出值以“,”进行分割
		 * @throws Exception
		 */
		public String callDBFuncProce(String functionName , String type ,Map<String,Object> map) throws Exception
		{
			
			String valueStr = "";
			
			if(map != null && map.size() > 0)
			{
				valueStr = getMapKeyValue(map);
			}

			
			Connection con = null;
			String value = "";
			String outValue = "";
			
			CallableStatement callableStatement = null;
			try
			{
				con = dbAccess.getConnection();
			
			
				if(type.equals("function"))
				{
					callableStatement = con.prepareCall("{? = call "+functionName+"(?,?)}");
					callableStatement.registerOutParameter(1, Types.VARCHAR);
					callableStatement.setString(2, valueStr);	
					callableStatement.registerOutParameter(3, Types.VARCHAR);
					callableStatement.execute();
					value = callableStatement.getString(1);
					outValue = callableStatement.getString(3);
				}
				else
				{
					callableStatement = con.prepareCall("{call "+functionName+"(?,?)}");
					callableStatement.setString(1, valueStr);				
					callableStatement.registerOutParameter(2, Types.VARCHAR);
					callableStatement.execute();
					outValue = callableStatement.getString(1);
				
				}
			}catch(Exception f)
			{
				f.fillInStackTrace();
			}
			finally
			{
				if (null!=con) con.close();
				if(null!=callableStatement)callableStatement.close();
			}
				return value +"&"+ outValue;
		}
		
		/**
		 * 取过程配套参数分割符
		 * @param map
		 * @return
		 * @throws Exception
		 */
		public String getMapKeyValue(Map<String,Object> map) throws Exception
		{
			String str ="";
			String str1 = SelResultStr("select chr(1) from dual");
		    String str2 = SelResultStr("select chr(2) from dual");
			for(Entry<String,Object> entry : map.entrySet())
			{
			    
				String key = entry.getKey();
				String value = (String) map.get(key);
				str = str + key + str2 + value + str1;
			}
			return str.substring(0,str.lastIndexOf(str1));
		}
}


